package com.dzl.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ReadExcelUtil {

    private static final String EXCEL_XLS = ".xls";
    private static final String EXCEL_XLSX = ".xlsx";

    /**
     * 读取excel数据
     * @throws Exception
     */
    public static JsonResult<List<List<String>>> readExcelInfo(File excelFile) throws Exception {
        // 支持excel2003、2007
        InputStream is = new FileInputStream(excelFile);// 创建输入流对象
        JsonResult<List<List<String>>> result = checkExcelVaild(excelFile);
        if (result.getCode() != 200) {
            is.close();
            return result;
        }
        Workbook workbook = getWorkBook(is, excelFile);
        // Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010
        // 获取Sheet数量
        int sheetNum = workbook.getNumberOfSheets();
        // 创建二维数组保存所有读取到的行列数据，外层存行数据，内层存单元格数据
        List<List<String>> dataList = new ArrayList<List<String>>();
        // FormulaEvaluator formulaEvaluator = null;
        // 遍历工作簿中的sheet,第一层循环所有sheet表
        for (int index = 0; index < sheetNum; index++) {
            Sheet sheet = workbook.getSheetAt(index);
            if (sheet == null) {
                continue;
            }
            // System.out.println("表单行数：" + sheet.getLastRowNum());
            // 如果当前行没有数据跳出循环，第二层循环单sheet表中所有行
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                // 根据文件头可以控制从哪一行读取，在下面if中进行控制
                if (row == null) {
                    continue;
                }
                // 遍历每一行的每一列，第三层循环行中所有单元格
                List<String> cellList = new ArrayList<String>();
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    // System.out.println("遍历行中cell数据:" + getCellValue(cell));
                    cellList.add(getCellValue(cell));
                    // System.out.println("第" + cellIndex + "个:     cell个数：" + cellList.size());
                }
                if (EmptyUtils.isEmpty(cellList)) {
                    continue;
                }
                if (EmptyUtils.isEmpty(cellList.get(0))
                        && EmptyUtils.isEmpty(cellList.get(1))
                        && EmptyUtils.isEmpty(cellList.get(2))
                        && EmptyUtils.isEmpty(cellList.get(3))) {//去除空行
                    continue;
                }
                dataList.add(cellList);
                //System.out.println("第" + rowIndex + "行:     共几行：" + dataList.size());
            }

        }
        is.close();
        return new JsonResult<List<List<String>>>(dataList, HttpCode.SUCCESS);
    }

    /**
     * 获取单元格的数据,暂时不支持公式
     */
    public static String getCellValue(Cell cell) {
        if (EmptyUtils.isEmpty(cell)) {
            return null;
        }
        String value = null;
        // 简单的查检列类型
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:// 字符串
                value = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字
                long dd = (long) cell.getNumericCellValue();
                value = dd + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                value = "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:// boolean型值
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                break;
        }
        return value;

    }

    /**
     * 判断excel的版本，并根据文件流数据获取workbook
     *
     * @throws IOException
     */
    public static Workbook getWorkBook(InputStream is, File file) throws Exception {

        Workbook workbook = null;
        if (file.getName().endsWith(EXCEL_XLS)) {
            workbook = new HSSFWorkbook(is);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {
            workbook = new XSSFWorkbook(is);
        }

        return workbook;
    }

    /**
     * 校验文件是否为excel
     *
     * @throws Exception
     */
    public static JsonResult<List<List<String>>> checkExcelVaild(File file) throws Exception {
        if (!file.exists()) {
            return new JsonResult<List<List<String>>>(null, 1, "文件不存在！");
        }
        if (!file.isFile() || ((!file.getName().endsWith(EXCEL_XLS) && !file.getName().endsWith(EXCEL_XLSX)))) {
            System.out.println(file.isFile() + "===" + file.getName().endsWith(EXCEL_XLS) + "==="
                    + file.getName().endsWith(EXCEL_XLSX));
            System.out.println(file.getName());
            return new JsonResult<List<List<String>>>(null, 1, "文件不是Excel");
        }
        return new JsonResult<List<List<String>>>(null, HttpCode.SUCCESS);
    }
}